package DB;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;

import BO.Category;


public class CategoryDB extends Category{
	
	protected CategoryDB(int nr, String name, int parent){
		super(nr,name,parent);
	}
	/**
	 * Get the name of the category with the specified number.
	 * @param nr - Category number to get name from.
	 * @return - The category name.
	 */
	public static String getCategoryNameByNr(int nr){
		Connection connection = ConnectionPool.getConnection();
		String name = null;
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select name from T_Category where category_id =" + nr + ";");
			if(rs.next())
				name = rs.getString("name");
			rs.close();
		} catch (SQLException e){
			
		}
		ConnectionPool.releaseConnection(connection);
		return name;
	}
	
	/**
	 * Add a category.
	 * @param name - Name of new category.
	 * @param parentId - Id for the new categorys parent.
	 */
	public static void addCategory(String name, int parentId){
		Connection connection = ConnectionPool.getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate("insert into T_CATEGORY (name,parent_id) values(\"" + name + "\"," + parentId + ");");
			stmt.close();
		} catch (SQLException e){
			e.printStackTrace();
		}
		ConnectionPool.releaseConnection(connection);
	}
	
	/**
	 * Edit the category with the specified id.
	 * @param categoryId - The id of the category to be edited.
	 * @param name - New name for the category.
	 */
	public static void editCategory(int categoryId, String name){
		Connection connection = ConnectionPool.getConnection();
		try {
			Statement stmt = connection.createStatement();
			stmt.executeUpdate("update T_CATEGORY set name = \"" + name + "\" where category_id = " + categoryId + ";");
			stmt.close();
		} catch (SQLException e){
			e.printStackTrace();
		}
		ConnectionPool.releaseConnection(connection);
	}

	/**
	 * Remove a category. All the children of the category gets the categories parent as parent.
	 * If category dosn't have a parent, remove will fail.
	 * @param id - Id for the category to be removed.
	 * @return - True or false, depending on if the category got removed.
	 */
	public static boolean removeCategory(int id){
		Connection connection = ConnectionPool.getConnection();
		boolean success = false;	
		try {
			connection.setAutoCommit(false);
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select parent_id from T_CATEGORY where category_id = " + id + ";");
			if(rs.next()){
				int parent = rs.getInt("parent_id");
				stmt.executeUpdate("update T_CATEGORY set parent_id = " + parent + " where parent_id = " + id + ";");
				stmt.executeUpdate("update T_ITEM set category = " + parent + " where category = " + id + ";");
				stmt.executeUpdate("DELETE FROM T_CATEGORY WHERE category_id = " + id +";");
				success = true;
			}
			if(success){
				connection.commit();
			}else{
				connection.rollback();
			}
			rs.close();
			stmt.close();
			connection.setAutoCommit(true);
		} catch(SQLException e ){
				success = false;
				try {
					connection.rollback();
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			e.printStackTrace();			
		}
		ConnectionPool.releaseConnection(connection);
		return success;
	}
	
	/**
	 * Get all child categories to a category.
	 * @param name - Name of the parent category.
	 * @return - The childs.
	 */
	public static ArrayList<Category> getSubCategory(String name){
		ArrayList<Category> sub = new ArrayList<Category>();
		Connection connection = ConnectionPool.getConnection();
		try {
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select * from T_CATEGORY where parent_id = (select category_id from T_CATEGORY where name = \"" + name + "\");");
			while(rs.next()){
				int nr = rs.getInt("category_id");
				String catName = rs.getString("name");
				int parent = rs.getInt("parent_id");
				sub.add(new CategoryDB(nr,catName,parent));
			}
			rs.close();
			stmt.close();
		}catch(SQLException e){}
		ConnectionPool.releaseConnection(connection);
		return sub;
	}
	
	/**
	 * Get the complete category tree.
	 * @return All categories and there relationship.
	 */
	public static ArrayList<Category> getCategoryTree(){
		ArrayList<Category> categories = new ArrayList<Category>();
		Connection connection = ConnectionPool.getConnection();
		try {
			if(connection == null)
				System.out.println("Connection NULL!!");
			Statement stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery("select * from T_CATEGORY;");
			while(rs.next()){
				int nr = rs.getInt("category_id");
				String name = rs.getString("name");
				int parent = rs.getInt("parent_id");
				categories.add(new CategoryDB(nr,name,parent));
			}
			rs.close();
		} catch (SQLException e){
			
		}
		Collections.sort(categories);
		boolean done = false;
		if(categories.size() > 0){
			while(!done){
				Category child = categories.get(0);
				categories.remove(0);
				int size = 0;
				for(int i = 0; i < categories.size(); i++){
					Category parent = categories.get(i);
					size += parent.getParent();
					if(parent.getNr() == child.getParent()){
						parent.addChild(child);
					}
				}
				done = size == 0;
			}
		}
		ConnectionPool.releaseConnection(connection);
		return categories;
	}

}
